package Server.dao;

import Server.entity.Shop;
import Server.entity.ShopOrder;
import Server.entity.Users;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

public interface ShopOrderDao extends JpaRepository<ShopOrder,String> {
    @Query(value = "SELECT * FROM shop_order WHERE DATE(create_Time)<=?STR_TO_DATE(?2, '%Y-%m-%d')and status = 'MAIL'",
            nativeQuery = true)
    List<ShopOrder> findByStatusAndCreateTime(String createTime);

    ShopOrder findByUuid(String uuid);
    @Query(value = "SELECT * FROM shop_order WHERE create_Time>=?1 and create_Time<=?2 and user_id = ?3",
            countQuery = "SELECT count(*) FROM shop_order WHERE create_Time>=?1 and create_Time<=?2 and user_id = ?3",
            nativeQuery = true)
    Page<ShopOrder> findByTime(String createTime, String endTime,String userId, Pageable pageable);

    @Query(value = "SELECT * FROM shop_order WHERE create_Time>=?1 and create_Time<=?2 ",
            countQuery = "SELECT count(*) FROM shop_order WHERE create_Time>=?1 and create_Time<=?2",
            nativeQuery = true)
    Page<ShopOrder> findByTime(String createTime, String endTime, Pageable pageable);

    @Query(value = "select * from shop_order as b LEFT JOIN invite_users as c on b.user_id = c.invite_id where c.user_id = ?1 and b.create_Time>=?2 and create_Time<=?3 and user_id = ?4",
            countQuery = "select * from shop_order as b LEFT JOIN invite_users as c on b.user_id = c.invite_id where c.user_id = ?1 and b.create_Time>=?2 and create_Time<=?3 and user_id = ?4",
            nativeQuery = true)
    Page<ShopOrder> findByTimeUS(String myid,String createTime, String endTime,String userId, Pageable pageable);

    @Query(value = "select * from shop_order as b LEFT JOIN invite_users as c on b.user_id = c.invite_id where c.user_id = ?1 and create_Time>=?2 and create_Time<=?3 ",
            countQuery = "select * from shop_order as b LEFT JOIN invite_users as c on b.user_id = c.invite_id where c.user_id = ?1 and create_Time>=?2 and create_Time<=?3",
            nativeQuery = true)
    Page<ShopOrder> findByTimeUS(String myid,String createTime, String endTime, Pageable pageable);


    @Query(value = "select * from shop_order as b LEFT JOIN invite_users as c on b.user_id = c.invite_id where c.user_id = ?1  ",
            countQuery = "select * from shop_order as b LEFT JOIN invite_users as c on b.user_id = c.invite_id where c.user_id = ?1 ",
            nativeQuery = true)
    Page<ShopOrder> findUSALL(String myid,Pageable pageable);


    @Query(value = "select * from shop_order as b LEFT JOIN invite_users as c on b.user_id = c.invite_id left join users as d on d.uuid =b.user_id where c.user_id = ?1 and d.name like ?2",
            countQuery = "select * from shop_order as b LEFT JOIN invite_users as c on b.user_id = c.invite_id left join users as d on d.uuid =b.user_id where c.user_id = ?1 and d.name like ?2",
            nativeQuery = true)
    Page<ShopOrder> findUSALL(String myid,String name,Pageable pageable);


    @Query(value = "SELECT DATE_FORMAT(create_time, '%Y-%m-%d') days, COUNT(1) COUNT FROM shop_order WHERE STATUS = ?3 AND DATE(create_time) >= STR_TO_DATE(?1, '%Y-%m-%d') AND DATE(create_time) <= STR_TO_DATE(?2, '%Y-%m-%d') GROUP BY days",
            nativeQuery = true)
    List<Object[]> findGoup(String createTime, String endTime,String status);

    @Query(value = "SELECT DATE_FORMAT(create_time, '%Y-%m-%d') days, COUNT(1) COUNT FROM shop_order WHERE STATUS = ?3 AND DATE(create_time) >= STR_TO_DATE(?1, '%Y-%m-%d') AND DATE(create_time) <= STR_TO_DATE(?2, '%Y-%m-%d') and user_id in (select invite_id from invite_users where user_id=?4) GROUP BY days",
            nativeQuery = true)
    List<Object[]> findGoup(String createTime, String endTime,String status,String userId);

    @Query(value = "SELECT DATE_FORMAT(create_time, '%Y-%m-%d') days, COUNT(1) COUNT FROM shop_order WHERE  DATE(create_time) >= STR_TO_DATE(?1, '%Y-%m-%d') AND DATE(create_time) <= STR_TO_DATE(?2, '%Y-%m-%d') GROUP BY days",
            nativeQuery = true)
    List<Object[]> findGoup(String createTime, String endTime);

    @Query(value = "SELECT DATE_FORMAT(create_time, '%Y-%m-%d') days, COUNT(1) COUNT FROM shop_order WHERE  DATE(create_time) >= STR_TO_DATE(?1, '%Y-%m-%d') AND DATE(create_time) <= STR_TO_DATE(?2, '%Y-%m-%d') and user_id in (select invite_id from invite_users where user_id=?3) GROUP BY days",
            nativeQuery = true)
    List<Object[]> findGoupBy(String createTime, String endTime,String userId);


    @Query(value = "SELECT DATE_FORMAT(create_time, '%Y-%m-%d') days, sum(money) COUNT FROM shop_order WHERE STATUS = ?3 AND DATE(create_time) >= STR_TO_DATE(?1, '%Y-%m-%d') AND DATE(create_time) <= STR_TO_DATE(?2, '%Y-%m-%d') GROUP BY days",
            nativeQuery = true)
    List<Object[]> findGoupNumber(String createTime, String endTime,String status);

    @Query(value = "SELECT DATE_FORMAT(create_time, '%Y-%m-%d') days, sum(money) COUNT FROM shop_order WHERE STATUS = ?3 AND DATE(create_time) >= STR_TO_DATE(?1, '%Y-%m-%d') AND DATE(create_time) <= STR_TO_DATE(?2, '%Y-%m-%d') and user_id in (select invite_id from invite_users where user_id=?4) GROUP BY days",
            nativeQuery = true)
    List<Object[]> findGoupNumber(String createTime, String endTime,String status,String userId);

    @Query(value = "select * from shop_order where ?1 ",
            countQuery = "select * from shop_order where ?1 ",
            nativeQuery = true)
    Page<ShopOrder> findSql(String sql,Pageable pageable);
}
